import mmap
import contextlib
import datetime
import os
import sqlite3
import pymysql
import Evtx.BinaryParser
from Evtx.Evtx import FileHeader
from Evtx.Views import evtx_file_xml_view
from xml.dom import minidom
from pandas import DataFrame


def Log_Get(evtxpath):
    '''
    将 evt 日志文件解析成 Dataframe 数据
    :param evtxpath:evt 格式的日志路径
    :return:
    '''
    try:
        with open(evtxpath, 'r') as f:
            with contextlib.closing(mmap.mmap(f.fileno(), 0, access=mmap.ACCESS_READ)) as buf:
                content_list = []
                fh = FileHeader(buf, 0)
                db_time = []
                db_type = []
                db_level = []

                # 遍历每条日志信息
                for xml, record in evtx_file_xml_view(fh):
                    content_dict = {}
                    domtree = minidom.parseString(xml)
                    # 获取事件标准信息（System）
                    content_dict['Channel'] = domtree.getElementsByTagName('Channel')[0].childNodes[0].data
                    content_dict['SystemTime'] = str(datetime.datetime.strptime(
                        domtree.getElementsByTagName('TimeCreated')[0].getAttribute('SystemTime')[:19],
                        '%Y-%m-%d %H:%M:%S') + datetime.timedelta(hours=8))
                    content_dict['EventID'] = domtree.getElementsByTagName('EventID')[0].childNodes[0].data
                    content_dict['Level'] = domtree.getElementsByTagName('Level')[0].childNodes[0].data
                    content_dict['UserID'] = domtree.getElementsByTagName('Security')[0].getAttribute('UserID')
                    # content_dict['ProcessID'] = domtree.getElementsByTagName('Execution')[0].getAttribute('ProcessID')
                    content_dict['Computer'] = domtree.getElementsByTagName('Computer')[0].childNodes[0].data
                    # sqlite_conn = sqlite3.connect("user_information.db")
                    # cur = sqlite_conn.cursor()
                    # cur.execute('''CREATE TABLE IF NOT EXISTS log_Windows_information(
                    #                                                                                        log_time TEXT,
                    #                                                                                        status TEXT,
                    #                                                                                        log_level TEXT,
                    #                                                                                        Computer TEXT);''')
                    # sqlite_conn.commit()
                    # cur.execute(f"INSERT INTO log_Windows_information(log_time,status,log_level,Computer) values('{content_dict['SystemTime']}','{content_dict['EventID']}','{content_dict['Level']}','{content_dict['Computer']}')")
                    # sqlite_conn.commit()
                    # sqlite_conn.close()

                    db = pymysql.connect(
                        host='localhost',
                        user='root',
                        password='123456',
                        database='maoxuechunqimozuoye'
                    )
                    py_cursor = db.cursor()
                    py_cursor.execute('''CREATE TABLE IF NOT EXISTS log_Windows_information(
                                                                                                                               log_time varchar(256),
                                                                                                                               status varchar(256),
                                                                                                                               log_level varchar(256),
                                                                                                                               Computer varchar(256))DEFAULT CHARSET=utf8;''')
                    db.commit()
                    py_cursor.execute(
                        f"INSERT INTO log_Windows_information(log_time,status,log_level,Computer) values('{content_dict['SystemTime']}','{content_dict['EventID']}','{content_dict['Level']}','{content_dict['Computer']}')")
                    db.commit()
                    db.close()
                    if content_dict['EventID']=='4625':
                        db_type.append('检测到SSH/RDP暴力破解')
                        db_time.append(content_dict['SystemTime'])
                        db_level.append(content_dict['Level'])


                    # 获取事件详情信息（EventData）
                    for data in domtree.getElementsByTagName('Data'):
                        if len(data.childNodes):
                            content_dict[data.getAttribute('Name') + '_EventData'] = data.childNodes[0].data

                    # 获取用户数据信息（UserData）
                    # EventData 与 UserData 一样都是3级子列表，都可以使用以下循环获取完整的日志信息。System 也可以，但需修改为2级
                    for eleone in domtree.getElementsByTagName('UserData'):
                        for eletwo in eleone.childNodes:
                            for elethree in eletwo.childNodes:
                                if elethree.childNodes != () and len(elethree.childNodes) == 1:  # 也可能存在4级，直接忽略
                                    content_dict[elethree.tagName + '_UserData'] = elethree.childNodes[0].data

                    # 归档日志信息
                    content_list.append(content_dict)
            if len(db_time)>3:
                # sqlite_conn = sqlite3.connect("user_information.db")
                # cur = sqlite_conn.cursor()
                # cur.execute('''CREATE TABLE IF NOT EXISTS warning_Windows_information(
                #                                                                                                                   attack_type TEXT,
                #                                                                                                                   log_time TEXT,
                #                                                                                                                   warning_level TEXT);''')
                # sqlite_conn.commit()
                # for i in range(len(db_time)):
                #     cur.execute(f"INSERT INTO warning_Windows_information(attack_type,log_time,warning_level) values('{db_type[i]}','{db_time[i]}','{db_level[i]}')")
                #     sqlite_conn.commit()
                # sqlite_conn.close()

                db = pymysql.connect(
                    host='localhost',
                    user='root',
                    password='123456',
                    database='maoxuechunqimozuoye'
                )
                py_cursor = db.cursor()
                py_cursor.execute('''CREATE TABLE IF NOT EXISTS warning_Windows_information(
                                                                                                                                                  attack_type varchar(256) ,
                                                                                                                                                  log_time attack_type varchar(256),
                                                                                                                                                  warning_level attack_type varchar(256))DEFAULT CHARSET=utf8;''')
                db.commit()
                for i in range(len(db_time)):
                    py_cursor.execute(
                        f"INSERT INTO warning_Windows_information(attack_type,log_time,warning_level) values('{db_type[i]}','{db_time[i]}','{db_level[i]}')")
                    db.commit()
                db.close()

            f.close()
        df = DataFrame(content_list).fillna('')  # 让 Nan 为空字符串
        # df = DataFrame(content_list).fillna('').sort_values(by=['SystemTime'])   # 按时间排序
        return df
    # 出现一些编码无法识别的日志文件，如果该日志不属于 ['Security', 'System', 'Setup']，则进行忽略
    except (Evtx.BinaryParser.OverrunBufferException, KeyError):
        if os.path.splitext(os.path.basename(evtxpath))[0] not in ['Security', 'System', 'Setup']:
            return DataFrame()
        else:
            raise



